package com.opoc.utils;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.*;
import org.apache.tika.mime.MimeTypeException;
import org.apache.tika.mime.MimeTypes;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.sql.Timestamp;
import java.util.*;

@Slf4j
public class ExcelUtils {
  public static final String STRING = "string";
  public static final String PICTURE = "picture";
  public static final String DATE = "date";
  public static final String INT = "int";
  public static final String DOUBLE = "double";
  public static final String BOOLEAN = "boolean";



  public static <T> List<T> getBeansFromExcel(MultipartFile file,
                                              Map<String, String> header,
                                              Map<String, String> header_sql,
                                              Class<T> beanClass) throws Exception {
    //从excel中获取map列表 maps -> javabean_sql
    List<Map<String, Object>> maps = getMapListFromExcel(file, header);
    if (maps == null) {
      return null;
    }

    Set<String> keys = header_sql.keySet();
    Map<String, Object> map_sql = new HashMap<>();

    List<T> Beans = new ArrayList<>();
    for (Map<String, Object> map : maps) {
      //根据 excel字段名 - sql 字段名 映射表 转换为 sql字段名对象
      for (String key : keys) {
        map_sql.put(header_sql.get(key), map.get(key));
      }
      T bean = JavaBeanUtils.mapToBean(map_sql, beanClass);
      Beans.add(bean);
    }
    return Beans;
  }

  /**
   * 从Excel文件中获取 excel每一行数据的List
   * 解析两个不同版本excel文件 xls xlsx
   * @return Workbook对象
   * @解析失败返回null
   */
  public static List<Map<String, Object>> getMapListFromExcel(MultipartFile file, Map<String, String> header) throws IOException, MimeTypeException {
    //MultipartFile spring提供的用来接收前端文件的类
    String fileExtension = FileUtils.getFileExtension(file);
    Workbook workbook;

    //保险措施
    if (FileUtils.EMPTY.equals(fileExtension)){ //如果文件名异常 直接返回null
      return null;
    } else if ("xls".equals(fileExtension)){ //xls文件读取
      //前端 -> MultipartFile -> POIFSFileSystem -> HSSFWorkbook -> Workbook
      workbook = new HSSFWorkbook(new POIFSFileSystem(file.getInputStream()));

    } else if ("xlsx".equals(fileExtension)){ //xlsx文件读取
      //前端 -> MultipartFile -> XSSFWorkbook -> Workbook
      workbook = new XSSFWorkbook(file.getInputStream());
    } else {
      return null;
    }
    return new ExcelUtils().getMapListFromWorkbook(workbook, header);
  }

  /**
   *从workbook中获取 excel每一行数据的List
   */
  private List<Map<String, Object>> getMapListFromWorkbook(Workbook workbook, Map<String, String> header) throws IOException, MimeTypeException {
    //存储结果
    List<Map<String, Object>> maps = new ArrayList<>();
    //存储表头字段名
    List<String> filedNameList = new ArrayList<>();
    if (workbook != null) {

      //多表只取第一张
      if (workbook.getNumberOfSheets() > 1) {
        log.warn("表单数量大于1张 只处理第一张表单");//等待Exception管理 由Exception统一报错
      }

      //取第一张表单
      Sheet sheet = workbook.getSheetAt(0);

      //获取表单中的图片List
      List<Map<HSSFClientAnchor, String>> XlsPictures = new ArrayList<>();
      List<Map<XSSFClientAnchor, String>> XlsXPictures = new ArrayList<>();

      if (sheet instanceof HSSFSheet) {
        XlsPictures = GetPictureListFromXlsSheet(sheet);
      } else if (sheet instanceof XSSFSheet) {
        XlsXPictures = GetPictureListFromXlsXSheet(sheet);
      }

      //遍历表中的所有行
      for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++){
        Row currentRow = sheet.getRow(j);

        if (j == sheet.getFirstRowNum()) {

            //获取表头
            for (int h = currentRow.getFirstCellNum(); h < currentRow.getLastCellNum(); h++) {
              //遍历表头行
              Cell cell = currentRow.getCell(h);

              //header必须是字符串类型
              if (cell.getCellType() != CellType.STRING){
                log.error("表头必须为字符串类型");
                return null;
              }
              filedNameList.add(cell.getRichStringCellValue().getString());
            }

            //校验表头
            if (!validateHeader(new ArrayList<>(header.keySet()), filedNameList)){
              log.error("表头不符合规定");
              return null;
            }

        } else {
          Map<String, Object> map = new HashMap<>();
          //遍历一行中的所有 字段值
          for (int m = currentRow.getFirstCellNum(); m < currentRow.getLastCellNum(); m++) {

            Cell cell = currentRow.getCell(m);
            if (cell == null){
              log.info("row:" + j + "  column:" + m + "   is null");
              continue;
            }
//=======================================================================================================================
            //filedNameList溢出 filedNameList.get(m);
            //===================
            String key = filedNameList.get(m);
            String cellFormatSetting = header.get(key);
            //检查单元格类型是否符合既定类型
            switch (cell.getCellType()){

              //单元格默认为String
              case STRING:
                //如果与既定的单元格类型符合
                if (STRING.equals(cellFormatSetting)) {
                  String string = cell.getRichStringCellValue().getString();
                  map.put(key, string);
                } else if (PICTURE.equals(cellFormatSetting)){
                  map.put(key, null);
                } else {
                  //否则 拒绝读取
                  return null;
                }
                break;

              case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)){
                  if (DATE.equals(cellFormatSetting)){
                    java.sql.Timestamp date = new java.sql.Timestamp(cell.getDateCellValue().getTime());
                    map.put(key, date);
                  } else if (PICTURE.equals(cellFormatSetting)){
                    map.put(key, null);
                  } else {
                    return null;
                  }

                } else {
                  if (DOUBLE.equals(cellFormatSetting)){
                    double number = cell.getNumericCellValue(); //返回值是double
                    map.put(key, number);
                  } else if (INT.equals(cellFormatSetting)){
                    int intNumber = (int) cell.getNumericCellValue();
                    map.put(key, intNumber);
                  } else if (PICTURE.equals(cellFormatSetting)){
                    map.put(key, null);
                  }
                }
                break;

              case BLANK:
                break;

              default:
                if (PICTURE.equals(cellFormatSetting)) {
                  map.put(key, null);
                } else {
                  return null;
                }
            }
          }
          maps.add(map);
        }
      }

      //获取完全部文字信息后 往返回的List中插入图片数据
      if (sheet instanceof HSSFSheet) {
        if (!XlsPictures.isEmpty()) {
          //遍历图片尝试插入 List
          XlsPictures.forEach(pic -> {
            Set<HSSFClientAnchor> keys = pic.keySet();
            keys.forEach(key -> {
              int row = key.getRow1() - 1;
              int col = key.getCol1();
              //文字优先 如果一个单元格中 有文字和图片 优先保存文字
              //单元格中没有文字 则存入图片数据
              maps.get(row).putIfAbsent(filedNameList.get(col), pic.get(key));
            });
          });
        }
      } else if (sheet instanceof XSSFSheet) {
        if (!XlsXPictures.isEmpty()) {
          //遍历图片尝试插入 List
          XlsXPictures.forEach(pic -> {
            Set<XSSFClientAnchor> keys = pic.keySet();
            keys.forEach(key -> {
              //去除表头 1行
              int row = key.getRow1() - 1;
              int col = key.getCol1();
              //文字优先 如果一个单元格中 有文字和图片 优先保存文字
              //单元格中没有文字 则存入图片数据
              maps.get(row).putIfAbsent(filedNameList.get(col), pic.get(key));
            });
          });
        }
      }
    }
    return clearEmptyRow(maps);
  }

  /**
   *从xls表单中获取 所有图片 及其 行列坐标 的List
   * @图片数据由base64编码 存储
   */
  private List<Map<HSSFClientAnchor, String>> GetPictureListFromXlsSheet(Sheet sheet) throws IOException {

    List<Map<HSSFClientAnchor, String>> pictureListXls = new ArrayList<>();

    //如果sheet属于xls
    if (sheet instanceof HSSFSheet) {
      HSSFSheet hssfSheet = (HSSFSheet) sheet;
      HSSFPatriarch drawingPatriarch = (hssfSheet).getDrawingPatriarch();

      if (drawingPatriarch != null) {
        //shape是一个抽象类 它包括excel中的 文本框、图片、图表等对象 这里获取了所有这些乱七八糟的对象
        List<HSSFShape> shapes = drawingPatriarch.getChildren();
        if (shapes != null) {
          //遍历这些乱七八糟的对象 找到 属于图片的shape对象
          for (HSSFShape shape : shapes) {

            Map<HSSFClientAnchor, String> map = new HashMap<>();

            if (shape instanceof HSSFPicture) {

              //获取图片在 excel中的行列位置 anchor
              HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor();
              //获取图片的byte数据
              HSSFPicture pic = (HSSFPicture) shape;
              HSSFPictureData pictureData = pic.getPictureData();
              //获取图片名称和数据
              String picName = pic.getFileName();
              byte[] data = pictureData.getData();
              String filePath = FileUtils.BytesSaveAsFile(data, picName);
              //把图片anchor和存储路径打包进map
              map.put(anchor, filePath);
            }
            pictureListXls.add(map);
          }
        }
      }
      return pictureListXls;
    //如果sheet属于xlsx
    } else {
      return null;
    }
  }

  /**
   *从xlsx表单中获取 所有图片 及其 行列坐标 的List
   * @图片数据用路径 存储
   */
  private List<Map<XSSFClientAnchor, String>> GetPictureListFromXlsXSheet(Sheet sheet) throws IOException, MimeTypeException {
    List<Map<XSSFClientAnchor, String>> pictureListXlsx = new ArrayList<>();

    //如果sheet属于xlsx
    if (sheet instanceof XSSFSheet) {
      XSSFSheet xssfSheet = (XSSFSheet) sheet;
      XSSFDrawing drawing = (xssfSheet).getDrawingPatriarch();

      if (drawing != null) {
        //shape是一个抽象类 它包括excel中的 文本框、图片、图表等对象 这里获取了所有这些乱七八糟的对象
        List<XSSFShape> shapes = drawing.getShapes();
        //遍历这些乱七八糟的对象 找到 属于图片的shape对象
        for (XSSFShape shape : shapes) {

          Map<XSSFClientAnchor, String> map = new HashMap<>();

          if (shape instanceof XSSFPicture) {

            //获取图片在 excel中的行列位置 anchor
            XSSFClientAnchor anchor = (XSSFClientAnchor) shape.getAnchor();
            //获取图片的byte数据
            XSSFPicture pic = (XSSFPicture) shape;
            XSSFPictureData pictureData = (pic).getPictureData();

            //获取图片名称和数据
            String picMime = pictureData.getMimeType();
            MimeTypes allTypes = MimeTypes.getDefaultMimeTypes();
            String extension = allTypes.forName(picMime).getExtension();

            byte[] data = pictureData.getData();
            String filePath = FileUtils.BytesSaveAsFile(data, extension);
            //把图片anchor和存储路径打包进map
            map.put(anchor, filePath);
          }
          pictureListXlsx.add(map);
        }
      }
      return pictureListXlsx;
    } else {
      return null;
    }
  }

  /**
   *清除List中的空行
   * @xls文档似乎有点小bug 应该问题不大:(
   */
  private List<Map<String, Object>> clearEmptyRow(List<Map<String, Object>> maps){
    for (int i = 0; i < maps.size(); i++) {
      boolean isEmptyRow = true;
      Collection<Object> values = maps.get(i).values();
      for (Object value : values){
        if (value != null) {
          isEmptyRow = false;
          break;
        }
      }
      if (isEmptyRow){
        maps.remove(i);
        i--;
      }
    }
    return maps;
  }

  /**
   * 校验表格的header
   */
  public static boolean validateHeader(List<String> headerList, List<String> filedNameList){
    if (headerList.size() != filedNameList.size()) {
      return false;
    } else {

      for (String header : headerList) {
        if (!filedNameList.contains(header)) {
          return false;
        }
      }
      return true;
    }
  }

  /**
   * 导出Excel文档 xlsx 返回生成文件的路径
   */
  public static <T> String ExcelGenerator(Map<String,String> headers, Map<String,String> headers_sql, List<T> beans, Class<T> clazz) throws IllegalAccessException, IOException, InstantiationException, NoSuchMethodException, InvocationTargetException {
    if (beans.isEmpty()){
      return null;
    }

    List<Object> javaBeans = new ArrayList<>();

    for (T bean : beans) {
      if (bean == null){
        continue;
      }

      //通过传递进来的java类 生成一个javaBean对象
      T javaBean = clazz.getDeclaredConstructor().newInstance();
      //获取该对象申明的所有字段值
      Field[] fields = javaBean.getClass().getDeclaredFields();

      // 遍历属性信息
      for (Field field : fields) {
        // 设置私有属性可访问
        field.setAccessible(true);
        // 获取属性值
        Object value = field.get(bean);
        // 将属性值注入到 JavaBean 对象中
        field.set(javaBean, value);
      }
      // 将 JavaBean 对象添加到 List 中
      javaBeans.add(javaBean);
    }

    //新建excel文件
    try (Workbook workbook = new XSSFWorkbook()){
      //新建表单
      Sheet sheet = workbook.createSheet("Sheet1");

      //创建表头 (中文)
      List<String> keys = new ArrayList<>(headers_sql.keySet());

      Row headerRow = sheet.createRow(0);
      for (int i = 0; i < keys.size(); i++ ){
        headerRow.createCell(i).setCellValue(keys.get(i));
      }

      //添加数据
      for (int i = 0; i < javaBeans.size(); i++ ){
        //添加行
        Row row = sheet.createRow(i + 1);
        //javabean转map 方便批量操作
        Map<String, Object> map = JavaBeanUtils.beanToMap(javaBeans.get(i));
        for (int j = 0; j < keys.size(); j++ ){
          //根据设定的不同数据类型 插入数据
          Object object = map.get(headers_sql.get(keys.get(j)));
          if (object != null){
            switch (headers.get(keys.get(j))){
              case INT:
                row.createCell(j).setCellValue((Integer) object);
                break;
              case DATE:
                row.createCell(j).setCellValue( TimeUtils.SqlDateToStringDate((Timestamp) object));
                break;
              case PICTURE:

                //数据库中存储图片的文件路径 通过路径获取图片
                String picturePath = (String) object;
                byte[] imageBytes = IOUtils.toByteArray(Files.newInputStream(Paths.get(picturePath)));
                int pictureIdx = workbook.addPicture(imageBytes, Workbook.PICTURE_TYPE_JPEG);
                // 创建绘图对象
                XSSFDrawing drawing = (XSSFDrawing) sheet.createDrawingPatriarch();

                // 创建锚点，指定图片位置和大小
                XSSFClientAnchor anchor = drawing.createAnchor( 0,0, 0, 0, j, i + 1, j+1, i+2);
                // 插入图片
                drawing.createPicture(anchor, pictureIdx);

                row.createCell(j).setCellValue("");
                break;
              case STRING:
                row.createCell(j).setCellValue((String) object);
                break;
              case DOUBLE:
                row.createCell(j).setCellValue((Double) object);
                break;
            }
          }


        }
      }
      //workbook 生成完毕
      File dir = new File(FileUtils.FILE_PATH);
      //如果目录不存在，则创建
      if (!dir.exists()) {
        dir.mkdirs();
      }
      String path = FileUtils.FILE_PATH + UUID.randomUUID() + ".xlsx";
      try (FileOutputStream fos = new FileOutputStream(path)){
        workbook.write(fos);
      }
      log.info(path);
      return path;
    }

  }

}
